package DBLayer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.Supplier;

public class DBSupplier implements IFDBSupplier{
	private Connection con;
	public DBSupplier(){
		con = DBConnection.getInstance().getDBcon();
	}
	public ArrayList<Supplier> getAllSupplier() {
		return miscWhere("");
	}
	public Supplier findSupplier(String cvr) {
		String wClause = "cvr = '"+cvr+"'";
		return singleWhere(wClause);
	}
	public Supplier searchSupplierCvr(String cvr) {
		String wClause = "cvr = '"+cvr+"'";
		return singleWhere(wClause);
	}
	public Supplier searchSupplierName(String sName) {
		String wClause = "sName = '"+sName+"'";
		return singleWhere(wClause);
	}
	public Supplier searchSupplierSPhoneNo(int sPhoneNo) {
		String wClause = "sPhoneNo = '"+sPhoneNo+"'";
		return singleWhere(wClause);
	}
	public Supplier searchSupplierEmail(String email) {
		String wClause = "email = '"+email+"'";
		return singleWhere(wClause);
	}
	public void insertSupplier(Supplier supplier) throws SQLException {
		String query = "Insert into Supplier values('" +
				supplier.getcvr()+"','" +
				supplier.getsName()+"','" +
				supplier.getsAddress()+"','" +
				supplier.getcountry()+"'," +
				supplier.getsPhoneNo()+",'" +
				supplier.getemail()+"')";
		System.out.println("Insert query: "+ query);
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeUpdate(query);
			stmt.close();
		}catch(SQLException sql){
			System.out.println("Sql exception inserting supplier: "+sql);
		}
	}
	public void updateSupplier(Supplier supplier) throws SQLException {
		Supplier supObj = new Supplier();
		
		String query = "UPDATE supplier() SET " +
				"sName = '"+supObj.getsName()+"'," +
				"sAddress = '"+supObj.getsAddress()+"'," +
				"country = '"+supObj.getcountry()+"'," +
				"sPhoneNo = "+supObj.getsPhoneNo()+"," +
				"email = '"+supObj.getemail()+"'" +
					"WHERE cvr = '"+supObj.getcvr()+"'";
		System.out.println("Update query for supplier: "+query);
		try{
			con.setAutoCommit(false);
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeUpdate(query);
			stmt.close();
			con.commit();
			con.setAutoCommit(true);
		}catch(Exception sql){
			System.out.println("Update exception in supplier: "+sql);
		}
	}
	public void deleteSupplier(Supplier supplier) throws SQLException {
		String query = "DELETE from Supplier where cvr ='" +
				supplier.getcvr()+"'";
		System.out.println(query);
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeUpdate(query);
			stmt.close();
		}catch(SQLException sql){
			System.out.println("Delete exception in supplier: "+sql);
		}
	}
	private Supplier singleWhere(String wClause){
		ResultSet results;
		Supplier supObj = new Supplier();
		String query = buildQuery(wClause);
		System.out.println(query);
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			if(results.next()){
				supObj = buildSupplier(results);
				stmt.close();
			}
		}catch(Exception ex){
			System.out.println("Single where query exception in supplier: "+ex);
		}
		return supObj;
	}
	private ArrayList<Supplier> miscWhere(String wClause){
		ResultSet results;
		ArrayList<Supplier> list = new ArrayList<Supplier>();
		
		String query = buildQuery(wClause);
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			while(results.next()){
				Supplier supObj = new Supplier();
				supObj = buildSupplier(results);
				list.add(supObj);
			}
			stmt.close();
		}catch(Exception ex){
			System.out.println("Select query exception - : "+ex);
			ex.printStackTrace();
		}
		return list;
	}
	private String buildQuery(String wClause){
		String query = "Select * from Supplier ";
		if(wClause.length()>0){
			query = query + "Where "+ wClause;
		}
		return query;
	}
	private Supplier buildSupplier(ResultSet results){
		Supplier supObj = new Supplier();
		try{
			supObj.setcvr(results.getString(1));
			supObj.setsName(results.getString(2));
			supObj.setsAddress(results.getString(3));
			supObj.setcountry(results.getString(4));
			supObj.setsPhoneNo(results.getInt(5));
			supObj.setemail(results.getString(6));
		}catch(Exception ex){
			System.out.println("Exception building the supplier: "+ ex);
			ex.printStackTrace();
		}
		return supObj;
	}
}